package principal.model;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.ArrayList;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import principal.model.vos.*;

public class TransaccionesBD {

	public static java.sql.Connection oConexion;
	ConnPostgres conexion = null;
	Statement st = null;
	
	public TransaccionesBD(){
		conectar();
	}
	
	public void conectar() {
		try {
			if(conexion == null){
				conexion = new ConnPostgres(); 
			}
			if(oConexion == null){
				oConexion = conexion.conectarBD();
			}
			if(st == null){
				st = oConexion.createStatement();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void closeConection(){
		//conexion.cerrarConexionBD();
	}

	public void saveArticulo(Articulo articulo) {
		try {
			conectar();
			boolean comitida;
			comitida = st.execute("Insert into Articulo values('"
					+ articulo.getId().toUpperCase() + "','"
					+ articulo.getNombre().toUpperCase() + "','"
					+ articulo.getDescripcion().toUpperCase() + "',"
					+ articulo.getPrecio() + ")");
			closeConection();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	public void deleteArticulo(String id) {
		try {
			String id1=id.toUpperCase();
			conectar();
			boolean comitida;
			comitida = st.execute("Delete from Articulo where IDArticulo = '"+id1+"'");
			closeConection();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	
//	public List<Articulo> findAllArticulo() {
//		List<Articulo> arts = new ArrayList<Articulo>();
	public List findAllArticulo() {
		List arts = new ArrayList ();

		try {
			conectar();
			ResultSet rsArticulo;
			rsArticulo = st.executeQuery("select * from articulo where idarticulo not in (select idarticulo from componenteskit)");
			while (rsArticulo.next()) {
				Articulo temp = new Articulo();
				temp.setId(rsArticulo.getString("IDArticulo").toUpperCase());
				temp.setNombre(rsArticulo.getString("Nombre").toUpperCase());
				temp.setPrecio(rsArticulo.getDouble("Precio"));
				temp.setDescripcion(rsArticulo.getString("Descripcion").toUpperCase());
				arts.add(temp);

			}
			return arts;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arts;
	}

	public Articulo findArticuloById(String id) {
		try {
			conectar();
			ResultSet rsArticulo;
			rsArticulo = st.executeQuery("Select * from Articulo where IDArticulo = '"+ id.toUpperCase() + "'");
			Articulo art = new Articulo();
			rsArticulo.next();
			art.setId(rsArticulo.getString("IDArticulo").toUpperCase());
			art.setNombre(rsArticulo.getString("Nombre").toUpperCase());
			art.setPrecio(rsArticulo.getDouble("Precio"));
			art.setDescripcion(rsArticulo.getString("Descripcion").toUpperCase());

			return art;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return new Articulo();

	}

//	public List<Articulo> findByName(String sNombre) {
//		List<Articulo> arts = new ArrayList<Articulo>();
	public List findByName(String sNombre) {
		List arts = new ArrayList ();

		try {
			conectar();
			ResultSet rsArticulo;
			rsArticulo = st.executeQuery("Select * from Articulo where Nombre like '%"	+ sNombre.toUpperCase() + "%'");
			while (rsArticulo.next()) {
				Articulo temp = new Articulo();
				temp.setId(rsArticulo.getString("IDArticulo").toUpperCase());
				temp.setNombre(rsArticulo.getString("Nombre").toUpperCase());
				temp.setPrecio(rsArticulo.getDouble("Precio"));
				temp.setDescripcion(rsArticulo.getString("Descripcion")
						.toUpperCase());
				arts.add(temp);
			}
			return arts;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arts;
	}

//	public List<Articulo> findArticuloByNameExactly(String sNombre) {
//		List<Articulo> arts = new ArrayList<Articulo>();
	public List findArticuloByNameExactly(String sNombre) {
		List arts = new ArrayList ();

		try {
			conectar();
			ResultSet rsArticulo;
			rsArticulo = st.executeQuery("Select * from Articulo where Nombre = '"	+ sNombre.toUpperCase() + "'");
			if (rsArticulo.next()) {
				Articulo temp = new Articulo();
				temp.setId(rsArticulo.getString("IDArticulo").toUpperCase());
				temp.setNombre(rsArticulo.getString("Nombre").toUpperCase());
				temp.setDescripcion(rsArticulo.getString("Descripcion").toUpperCase());
				temp.setPrecio(rsArticulo.getDouble("Precio"));
				arts.add(temp);
			}
			return arts;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arts;
	}
	//Kit

	public void saveKit(Kit kit) {
		try {
			conectar();
			boolean comitida;
			comitida = st.execute("Insert into Kit values('"
					+ kit.getId().toUpperCase() + "','"
					+ kit.getNombre().toUpperCase() + "','"
					+ kit.getDescripcion().toUpperCase() + "','"
					+ kit.getTemporada().toUpperCase() + "')");

			//ArrayList<ArticuloComponente> arts = kit.getChildren();
			ArrayList arts = ((ArticuloComponente)kit).getChildren();

			for (int i=0; i < arts.size(); i++) {
				this.setArticuloOnKit(kit, (Articulo) arts.get(i));
			}
			
//			for (ArticuloComponente art : arts) {
//				this.setArticuloOnKit(kit, (Articulo) art);
//			}
			//conexion.cerrarConexionBD();
			closeConection();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	public void setArticuloOnKit(Kit kit, Articulo articulo) {
		try {
			conectar();
			boolean comitida;
			comitida = st.execute("Insert into ComponentesKit values('"
					+ kit.getId().toUpperCase() + "','"
					+ articulo.getId().toUpperCase() + "')");
			this.stockRemove(articulo);
			closeConection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void deleteKit(String id) {
		try {
			//List<String> arts = new ArrayList<String>();
			List  arts = new ArrayList ();
			ResultSet rsArticulo;
			conectar();
			boolean comitida;
			rsArticulo = st.executeQuery("Select * from componentesKit where IDKit = '"	+ id.toUpperCase() + "'");
			while (rsArticulo.next()) {
				String temp;
				
				temp=(rsArticulo.getString("IDArticulo").toUpperCase());
				
				arts.add(temp);
			}

			comitida = st.execute("Delete from componentesKit where  IDKit = '"	+ id.toUpperCase()+"'");
			
			deleteArticuloOnKit(arts);
			
			comitida = st.execute("Delete from Kit where  IDKit = '"+ id.toUpperCase()+"'");
			closeConection();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	
	//public void deleteArticuloOnKit(List<String> arts){
	public void deleteArticuloOnKit(List arts){
		
		try {
			conectar();
			boolean comitida;
			
			for(int i =0; i<arts.size() ;i++){
				comitida = st.execute("Delete from Articulo where  IDArticulo = '"	+ (String)arts.get(i).toString().toUpperCase() +"'");
			}

			//Este es el FOR de arriba
//			for(String art : arts){
//				comitida = st.execute("Delete from Articulo where  IDArticulo = '"	+ art.toUpperCase()+"'");
//			}
				
		}catch (SQLException e) {
			e.printStackTrace();
			}
	}

	// Busquedas Kits

//	public List<Kit> findAllKits() {
	public List findAllKits() {
		
//		List<Kit> kits = new ArrayList<Kit>();
		List kits = new ArrayList ();
		
		try {
			conectar();
			ResultSet rsKit;
			rsKit = st.executeQuery("Select * from Kit");

			while (rsKit.next()) {
				Kit temp = new Kit();
				temp.setId(rsKit.getString("IDKit").toUpperCase());
				temp.setNombre(rsKit.getString("Nombre").toUpperCase());
				temp.setDescripcion(rsKit.getString("Descripcion").toUpperCase());
				temp.setTemporada(rsKit.getString("Temporada").toUpperCase());
				kits.add(temp);
			}

			for (int i=0; i<=kits.size(); i++){
				List arts = this.findArticuloOnKit((Kit)kits.get(i));
				for(int j=0; j<=arts.size(); j++){
					((Kit)kits.get(i)).add((ArticuloComponente)arts.get(j));
//					k.add(art);
				}				
			}
			
//			FOR  de arriba
//			for (Kit k : kits){
//				List<Articulo> arts = this.findArticuloOnKit(k);;
//				for(Articulo art : arts){
//					k.add(art);
//				}				
//			}
		
			return kits;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return kits;
	}

	public Kit findKitById(String id) {
		try {
			conectar();
			ResultSet rsKit;
			//st = oConexion.createStatement();
			rsKit = st.executeQuery("Select * from Kit where IDKit = '"	+ id.toUpperCase() + "'");
			Kit kit = new Kit();
			rsKit.next();
			kit.setId(rsKit.getString("IDKit").toUpperCase());
			kit.setNombre(rsKit.getString("Nombre").toUpperCase());
			kit.setDescripcion(rsKit.getString("Descripcion").toUpperCase());
			kit.setTemporada(rsKit.getString("Temporada").toUpperCase());

			List arts = this.findArticuloOnKit(kit);
			for(int i=0; i<arts.size(); i++){
				//((Kit)arts.get(i)).add()
				kit.add((ArticuloComponente)arts.get(i));
				//kit.add(art);
			}
			
			//Arriba
//			List<Articulo> arts = this.findArticuloOnKit(kit);
//			for(Articulo art : arts){
//				kit.add(art);
//			}
			return kit;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// este return solo lo puse para que no me marcara error el metodo pero
		// debe regresar el articulo que encontro en la b.d.
		return new Kit();

	}

//	public List<Kit> findKitByName(String sNombre) {
//		List<Kit> kits = new ArrayList<Kit>();
	public List findKitByName(String sNombre) {
		List kits = new ArrayList ();

		try {
			conectar();
			ResultSet rsKit;
			Kit temp;
			rsKit = st.executeQuery("Select * from Kit where Nombre like '%"+ sNombre.toUpperCase() + "%'");
//			ArrayList<Kit> listArtOnKit = new ArrayList<Kit>(); 
			ArrayList listArtOnKit = new ArrayList ();
			while (rsKit.next()) {
				temp = new Kit();
				temp.setId(rsKit.getString("IDKit").toUpperCase());
				temp.setNombre(rsKit.getString("Nombre").toUpperCase());
				temp.setDescripcion(rsKit.getString("Descripcion").toUpperCase());
				temp.setTemporada(rsKit.getString("Temporada").toUpperCase());
				listArtOnKit.add(temp);
			}

			//Roos2
//			Iterator<Kit> artsKits = listArtOnKit.iterator();
			Iterator artsKits = listArtOnKit.iterator();
			
			temp = new Kit();
			while(artsKits.hasNext()){
				
				//Roos2
//				List<Articulo> arts = this.findArticuloOnKit(artsKits.next());
				List  arts = this.findArticuloOnKit((Kit)(artsKits.next()));
				
				for(int i =0; i<=arts.size(); i++){
					temp.add((Articulo)arts.get(i));
				}
				//for de arriba
//				for(Articulo art : arts){
//					temp.add(art);
//				}
				kits.add(temp);
			}
			return kits;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return kits;
	}

//	public List<Kit> findKitByNameExactly(String sNombre) {
	//List<Kit> kits = new ArrayList<Kit>();
	public List findKitByNameExactly(String sNombre) {
		List kits = new ArrayList ();

		try {
			conectar();
			ResultSet rsKit;
			rsKit = st.executeQuery("Select * from  Kit where Nombre = '"+ sNombre.toUpperCase() + "'");
			if (rsKit.next()) {
				Kit temp = new Kit();
				temp.setId(rsKit.getString("IDKit").toUpperCase());
				temp.setNombre(rsKit.getString("Nombre").toUpperCase());
				temp.setDescripcion(rsKit.getString("Descripcion").toUpperCase());
				temp.setTemporada(rsKit.getString("Temporada").toUpperCase());
			
				//Roos2
//				List<Articulo> arts = this.findArticuloOnKit(temp);
				List arts = this.findArticuloOnKit(temp);

				for(int i=0; i<=arts.size();i++){
					
					temp.add((Articulo)arts.get(i));
				}
				//for de arriba
				//				for(Articulo art : arts){
				//					temp.add(art);
				//				}
				kits.add(temp);
			}
			return kits;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return kits;
	}

//	public List<Kit> findKitByTemporada(String temporada) {
//		List<Kit> kits = new ArrayList<Kit>();
	public List findKitByTemporada(String temporada) {
		List kits = new ArrayList ();
		try {
			conectar();
			ResultSet rsKit;
			rsKit = st.executeQuery("Select * from  Kit where Nombre = '"
					+ temporada.toUpperCase() + "'");
			while (rsKit.next()) {
				Kit temp = new Kit();
				temp.setId(rsKit.getString("IDKit").toUpperCase());
				temp.setNombre(rsKit.getString("Nombre").toUpperCase());
				temp.setDescripcion(rsKit.getString("Descripcion")
						.toUpperCase());
				temp.setTemporada(rsKit.getString("Temporada").toUpperCase());

				//Roos2
//				List<Articulo> arts = this.findArticuloOnKit(temp);
				List arts = this.findArticuloOnKit(temp);
				
				for(int i=0; i<=arts.size();i++){
					temp.add((Articulo)arts.get(i));
				}
				
//FOR de arriba				
//				for(Articulo art : arts){
//					temp.add(art);
//				}
				kits.add(temp);
			}
			return kits;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return kits;

	}
	
//	public List<Articulo> findArticuloOnKit(Kit kit){
//
//		List <Articulo> arts = new ArrayList<Articulo>();
	public List findArticuloOnKit(Kit kit){
		List  arts = new ArrayList ();
		
		try {
			conectar();
			ResultSet rsArt;
			String query = "Select * from ComponentesKit where IDKit ='" + kit.getId() + "'";
			rsArt = st.executeQuery(query);
//			ArrayList<String> iDsArts = new ArrayList<String>(); 
			ArrayList iDsArts = new ArrayList ();
			while (rsArt.next()) {
				iDsArts.add(rsArt.getString("IDArticulo"));
			}

			//Roos3
			for (int i=0; i<iDsArts.size();i++){	
//			for (int i=0; i<=iDsArts.size();i++){				
				arts.add(this.findArticuloById(iDsArts.get(i).toString()));
			}
			
//FOR arriba			
//			for (String idsArt : iDsArts){
//				arts.add(this.findArticuloById(idsArt));
//			}
			return arts;
		
		}catch (SQLException e) {
			e.printStackTrace();
			}
		return arts;
		}
			
	

	// Manejo de stock

	public void stockAdd(ArticuloComponente componente) {
		conectar();
		String nombre;
		int cantidad = 1;
		if (componente instanceof Articulo) {
			Articulo stockde = (Articulo) componente;
			nombre = stockde.getNombre().toUpperCase();
			int stock = this.getStock(stockde);
			if (stock != 0) {
				this.stockIncrement(stockde);
			} else {
				try {
					conectar();
					boolean comitida;
					comitida = st.execute("Insert into Stock values('" + nombre	+ "'," + cantidad + ")");
					closeConection();
				} catch (SQLException e) {
					// TODO: handle exception
					e.printStackTrace();
				}

			}
		}

		else {
			Kit stockde = (Kit) componente;
			nombre = stockde.getNombre().toUpperCase();
			int stock = this.getStock(stockde);
			if (stock != 0) {
				this.stockIncrement(stockde);
			} else {
				try {
					conectar();
					boolean comitida;
					comitida = st.execute("Insert into Stock values('" + nombre	+ "'," + cantidad + ")");
					closeConection();
				} catch (SQLException e) {
					// TODO: handle exception
					e.printStackTrace();
				}

			}
		}

	}

	public void stockIncrement(ArticuloComponente componente) {
		int stock;
		String nombre;
		if (componente instanceof Articulo) {
			Articulo stockde = (Articulo) componente;
			nombre = stockde.getNombre().toUpperCase();
		}

		else {
			Kit stockde = (Kit) componente;
			nombre = stockde.getNombre().toUpperCase();
		}
		try {
			conectar();
			ResultSet rComponente;

//			HashMap<String,Integer> dataStock = new HashMap<String,Integer>(); 
			HashMap dataStock = new HashMap ();
			
			rComponente = st.executeQuery("Select * from Stock where Nombre='"	+ nombre + "'");
//			ArrayList<Integer> stockLis = new ArrayList<Integer>();
//			ArrayList<String> namestockLis = new ArrayList<String>();
			ArrayList stockLis = new ArrayList ();
			ArrayList  namestockLis = new ArrayList ();
			if (rComponente.next()) {
				stock = rComponente.getInt("Cantidad");
				stock++;
				stockLis.add(new Integer(stock));
				namestockLis.add(nombre);
			}

			Iterator itNameStock = namestockLis.iterator();
			int key=0;
			while (itNameStock.hasNext()) {
				String query = "Update Stock Set Cantidad=" + stockLis.get(key) + " where Nombre='" + itNameStock.next() + "'";
				key++;
				boolean ok = st.execute(query);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		closeConection();

	}

	public void stockRemove(ArticuloComponente componente) {

		int stock;
		boolean comitida;
		String nombre;
		String id;
		if (componente instanceof Articulo) {
			Articulo stockde = (Articulo) componente;
			nombre = stockde.getNombre().toUpperCase();
			id=stockde.getId().toUpperCase();
		} else {
			Kit stockde = (Kit) componente;
			nombre = stockde.getNombre().toUpperCase();
			id=stockde.getId().toUpperCase();
		}

		try {
			conectar();
			ResultSet rComponente;
			rComponente = st.executeQuery("Select * from Stock where Nombre='"	+ nombre + "'");
			if (rComponente.next()) {
				stock = rComponente.getInt("Cantidad");
				stock--;
				comitida = st.execute("Update Stock Set Cantidad=" + stock	+ " where Nombre='" + nombre + "'");
			//comitida = st.execute("Delete from Articulo where IDArticulo = "
				//		+ id.toUpperCase());
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		closeConection();
	}

	public int getStock(ArticuloComponente componente) {

		int stock = 0;
		String nombre;
		if (componente instanceof Articulo) {
			Articulo stockde = (Articulo) componente;
			nombre = stockde.getNombre().toUpperCase();
		} else {
			Kit stockde = (Kit) componente;
			nombre = stockde.getNombre().toUpperCase();
		}

		try {

			conectar();
			ResultSet rComponente;
			rComponente = st.executeQuery("Select * from Stock where Nombre='"	+ nombre + "'");
			if (rComponente.next()) {
				stock = rComponente.getInt("Cantidad");
			}
			closeConection();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return stock;
	}

	// Codigo de seguridad

	public void saveCodigoSeguridad(String idProducto, CodigoSeguridad codigo) {

		try {
			conectar();
			boolean comitida;
			//st = oConexion.createStatement();
			comitida = st.execute("Insert into CodigoSeguridad values('"+ idProducto + "','" + codigo.getCodSeg() + "')");
			closeConection();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}

	public void removeCodigoSeguridad(String idProducto) {
		try {
			conectar();
			boolean comitida;
			comitida = st
					.execute("Delete from CodigoSeguridad where  IDArticulo = '"+ idProducto + "'");
			closeConection();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}		
	
//	public List<Kit> findAllKitsByTemp(String temporada) {
//		List<Kit> kits = new ArrayList<Kit>();
	public List findAllKitsByTemp(String temporada) {
		List  kits = new ArrayList ();
		
		try {
			conectar();
			ResultSet rsKit;
			rsKit = st.executeQuery("Select * from  Kit where temporada = '"
					+ temporada.toUpperCase() + "'");

			while (rsKit.next()) {
				Kit temp = new Kit();
				temp.setId(rsKit.getString("IDKit").toUpperCase());
				temp.setNombre(rsKit.getString("Nombre").toUpperCase());
				temp.setDescripcion(rsKit.getString("Descripcion").toUpperCase());
				temp.setTemporada(rsKit.getString("Temporada").toUpperCase());
				kits.add(temp);
			}

			for (int i =0; i<kits.size();i++){
				List arts = this.findArticuloOnKit((Kit)(kits.get(i)));
				for(int j=0; j<arts.size(); j++){
					((Kit) kits.get(i)).add((Articulo)arts.get(j));
				}				
			}
			
//FOR arriba			
//			for (Kit k : kits){
//				List<Articulo> arts = this.findArticuloOnKit(k);;
//				for(Articulo art : arts){
//					k.add(art);
//				}				
//			}
		
			return kits;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return kits;
	}
	
}
